﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;

namespace NORM.DataBase
{
    #region SqlHelper

    /// <summary>
    /// Sql 数据库操作类
    /// </summary>
    internal class SqlHelper
    {
        public SqlTransaction tran = null;

        public int Execute(string connectionString, List<string> cmdTextList)
        {
            return 0;
        }

        public int Execute(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public int Execute(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                return cmd.ExecuteNonQuery();
            }
        }

        public object GetScalar(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    return cmd.ExecuteScalar();
                }
            }
        }

        public object GetScalar(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                return cmd.ExecuteScalar();
            }
        }

        public DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        DataSet ds = new DataSet();
                        da.Fill(ds);
                        return ds;
                    }
                }
            }
        }

        public DataSet GetDataSet(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                DataSet ds = new DataSet();
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                try
                {
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        da.Fill(ds);
                        return ds;
                    }
                }
                catch (Exception ex)
                {
                    ds = new DataSet();
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    DataTable table = new DataTable("Table");
                    DataTable schemaTable = reader.GetSchemaTable();
                    foreach (DataRow dataRow in (InternalDataCollectionBase)schemaTable.Rows)
                    {
                        string typeName = string.Concat(dataRow["DataType"]);
                        string columnName = string.Concat(dataRow["ColumnName"]);
                        switch (typeName)
                        {
                            case "SqlTypes.BitString":
                                typeName = "System.Boolean";
                                break;
                        }
                        table.Columns.Add(new DataColumn(columnName, Type.GetType(typeName)));
                    }

                    while (reader.Read())
                    {
                        DataRow row = table.NewRow();
                        foreach (DataColumn dc in table.Columns)//InternalDataCollectionBase
                        {
                            object Value = reader[dc.ColumnName];
                            if (Value != null && !DBNull.Value.Equals(Value))
                                row[dc.ColumnName] = Value;
                        }
                        table.Rows.Add(row);
                    }

                    reader.Dispose();
                    ds.Tables.Add(table);
                    ds.Tables.Add(schemaTable);

                    //未开启事务，则查询完成关闭连接
                    //if (!TransactionBegin)
                    //    conn.Close();

                }
                return ds;
            }
        }

        public DataTable GetDataTable(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return GetDataSet(connectionString, cmdType, cmdText, cmdParms).Tables[0];
        }

        public DataTable GetDataTable(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            return GetDataSet(conn, cmdType, cmdText, cmdParms).Tables[0];
        }

        public DbDataReader GetReader(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
        }

        public DbDataReader GetReader(DbConnection conn, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                PrepareCommand(cmd, conn, tran, cmdType, cmdText, cmdParms);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

        public DataTable GetDbSchema(string connectionString)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("id", typeof(Int32)));
            dt.Columns.Add(new DataColumn("table_name", typeof(string)));
            dt.Columns.Add(new DataColumn("table_type", typeof(string)));

            StringBuilder strSql = new StringBuilder();
            strSql.Append("  select [type] as [table_type] ,[tbl_name] as [table_name] from Sql_master  ");

            DataTable dt_schema = GetDataTable(connectionString, CommandType.Text, strSql.ToString(), null);

            int index = 1;
            foreach (DataRow dr in dt_schema.Rows)
            {
                DataRow newrow = dt.NewRow();
                newrow["id"] = index;
                newrow["table_name"] = dr["table_name"] + "";
                string ttype = (dr["table_type"] + "").Trim().ToLower();
                switch (ttype)
                {
                    case "u": ttype = "table"; break;
                    case "v": ttype = "view"; break;
                    case "p": ttype = "procedure"; break;
                }
                newrow["table_type"] = ttype;
                dt.Rows.Add(newrow);
                index++;
            }
            dt_schema.Dispose();

            return dt;
        }

        public DataTable GetDbSchema(string connectionString, string collectionName)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("id", typeof(Int32)));
            dt.Columns.Add(new DataColumn("table_name", typeof(string)));
            dt.Columns.Add(new DataColumn("table_type", typeof(string)));

            StringBuilder strSql = new StringBuilder();
            strSql.Append("  select [type] as [table_type] ,[tbl_name] as [table_name] from Sql_master  ");

            DataTable dt_schema = GetDataTable(connectionString, CommandType.Text, strSql.ToString(), null);

            int index = 1;
            foreach (DataRow dr in dt_schema.Rows)
            {
                DataRow newrow = dt.NewRow();
                newrow["id"] = index;
                newrow["table_name"] = dr["table_name"] + "";
                string ttype = (dr["table_type"] + "").Trim().ToLower();
                switch (ttype)
                {
                    case "u": ttype = "table"; break;
                    case "v": ttype = "view"; break;
                    case "p": ttype = "procedure"; break;
                }
                newrow["table_type"] = ttype;
                dt.Rows.Add(newrow);
                index++;
            }
            dt_schema.Dispose();

            return dt;
        }

        public bool AddColumDescribtion(string connectionString, string tableName, string columnName, string Descrition)
        {
            SqlParameter[] parameters = { 
                    new SqlParameter ("@name",SqlDbType.VarChar,50), 
                    new SqlParameter ("@value",SqlDbType.VarChar,50), 
                    new SqlParameter ("@level0type",SqlDbType.VarChar,50), 
                    new SqlParameter ("@level0name",SqlDbType.VarChar,50), 
                    new SqlParameter ("@level1type",SqlDbType.VarChar,50), 
                    new SqlParameter ("@level1name",SqlDbType.VarChar,50), 
                    new SqlParameter ("@level2type",SqlDbType.VarChar,50),
                    new SqlParameter ("@level2name",SqlDbType.VarChar,50)                    
                                        };

            parameters[0].Value = "MS_Description";
            parameters[1].Value = Descrition;
            parameters[2].Value = "USER";
            parameters[3].Value = "dbo";
            parameters[4].Value = "TABLE";
            parameters[5].Value = tableName;
            parameters[6].Value = "column";
            parameters[7].Value = columnName;

            int v = Execute(connectionString, CommandType.StoredProcedure, "sp_addextendedproperty", parameters);
            if (v > 0)
                return true;
            else return false;
        }

        public DataTable GetTables(string connectionString)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("id", typeof(Int32)));
            dt.Columns.Add(new DataColumn("table_name", typeof(string)));
            dt.Columns.Add(new DataColumn("table_type", typeof(string)));
            string strSql = " select [id], [name] as [table_name] ,[type] as [table_type] from [sysobjects] where [type] in  ('U','V','P') order by [type] asc , [name] asc ";
            DataTable dt_schema = GetDataSet(connectionString, CommandType.Text, strSql, null).Tables[0];
            int index = 1;
            foreach (DataRow dr in dt_schema.Rows)
            {
                DataRow newrow = dt.NewRow();
                newrow["id"] = index;
                newrow["table_name"] = dr["table_name"] + "";
                string ttype = (dr["table_type"] + "").Trim().ToLower();
                switch (ttype)
                {
                    case "u": ttype = "table"; break;
                    case "v": ttype = "view"; break;
                    case "p": ttype = "procedure"; break;
                }
                newrow["table_type"] = ttype;
                dt.Rows.Add(newrow);
                index++;
            }
            dt_schema.Dispose();
            return dt;
        }

        public DataTable GetTableColumns(string connectionString, string tableName)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("colorder", typeof(Int32)));
            dt.Columns.Add(new DataColumn("ColumnName", typeof(string)));
            dt.Columns.Add(new DataColumn("TypeName", typeof(string)));
            dt.Columns.Add(new DataColumn("Length", typeof(string)));
            dt.Columns.Add(new DataColumn("CisNull", typeof(string)));
            dt.Columns.Add(new DataColumn("Describ", typeof(string)));

            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * ");
            strSql.Append("from ( ");
            strSql.Append("select ");
            strSql.Append("d.Colorder, ");
            strSql.Append("d.ColumnName, ");
            strSql.Append("d.TypeName, ");
            strSql.Append("d.Length, ");
            strSql.Append("d.CisNull, ");
            strSql.Append("e.value as Describ ");
            strSql.Append("from ( ");
            strSql.Append("select ");
            strSql.Append("a.colorder as Colorder,");
            strSql.Append("a.name as ColumnName,");
            strSql.Append("b.name as TypeName, ");
            strSql.Append("a.length as Length, ");
            strSql.Append("a.isnullable as CisNull, ");
            strSql.Append("a.colid as Colid, ");
            strSql.Append("a.id as ColumnID ");
            strSql.Append("from syscolumns a, systypes b, sysobjects c ");
            strSql.Append("where a.xtype = b.xusertype ");
            strSql.Append("and a.id = c.id ");
            strSql.Append("and c.name ='" + tableName + "' ");
            strSql.Append(") d ");
            strSql.Append("left join sys.extended_properties e ");
            strSql.Append("on d.Colid=e.minor_id  ");
            strSql.Append("and d.ColumnID=e.major_id ");
            strSql.Append("and e.name='MS_Description' ");
            strSql.Append(") f ");
            strSql.Append("order by colorder");

            DataTable dt_pk_columns = GetTabPKeyColumns(connectionString, tableName);
            DataTable dt_identity_columns = GetTabIdentityColumns(connectionString, tableName);
            DataTable dt_columns = GetDataSet(connectionString, CommandType.Text, strSql.ToString(), null).Tables[0];

            int index = 0;
            foreach (DataRow dr in dt_columns.Rows)
            {
                string cisnull = "";
                DataRow newrow = dt.NewRow();
                newrow["Colorder"] = index;
                newrow["ColumnName"] = dr["ColumnName"] + "";

                string ctype = dr["TypeName"] + "";
                newrow["TypeName"] = ctype;

                string len = dr["Length"] + "";
                if (len == "-1")
                    len = "max";

                newrow["length"] = len;

                switch (ctype)
                {
                    case "datetime": len = ""; break;
                    case "int": len = ""; break;
                    case "bigint": len = ""; break;
                    case "bit": len = ""; break;
                }

                foreach (DataRow sub_dr in dt_pk_columns.Rows)
                {
                    if ((sub_dr["Column_Name"] + "").Trim().Equals((dr["ColumnName"] + "").Trim()))
                    {
                        cisnull += "pk";
                    }
                }
                cisnull += ",";

                string _cisnull = (dr["CisNull"] + "").Trim();
                if (_cisnull != "0")
                {
                    cisnull += "null,";
                }
                else
                {
                    cisnull += "not null,";
                }

                string column_description = dr["Describ"] + "";


                foreach (DataRow sub_dr in dt_identity_columns.Rows)
                {
                    if ((sub_dr["Type_Name"] + "").Contains("identity"))
                    {
                        if ((sub_dr["Column_Name"] + "").Trim().Equals((dr["ColumnName"] + "").Trim()))
                        {
                            cisnull += "identity";
                        }
                    }
                }

                newrow["CisNull"] = cisnull;
                newrow["Describ"] = column_description;
                dt.Rows.Add(newrow);
                index++;
            }

            return dt;
        }

        public DataTable GetTabPKeyColumns(string connectionString, string TableName)
        {
            DbParameter[] parameters = new DbParameter[] {
					new SqlParameter("@table_name", SqlDbType.NVarChar,384),
					new SqlParameter("@table_owner", SqlDbType.NVarChar,384),
                    new SqlParameter("@table_qualifier", SqlDbType.NVarChar,384) 
                        };

            parameters[0].Value = TableName;
            parameters[1].Value = "dbo";
            parameters[2].Value = null;

            return GetDataSet(connectionString, CommandType.StoredProcedure, "sp_pkeys", parameters).Tables[0];
        }

        public DataTable GetTabIdentityColumns(string connectionString, string TableName)
        {
            DbParameter[] parameters = new DbParameter[] {
					new SqlParameter("@table_name", SqlDbType.NVarChar,384),
					new SqlParameter("@table_owner", SqlDbType.NVarChar,384),
                    new SqlParameter("@table_qualifier", SqlDbType.NVarChar,384) 
                        };

            parameters[0].Value = TableName;
            parameters[1].Value = "dbo";
            parameters[2].Value = null;

            return GetDataSet(connectionString, CommandType.StoredProcedure, "sp_columns", parameters).Tables[0];
        }

        public bool RenameTable(string connectionString, string OldName, string NewName)
        {
            return false;
        }

        public DataSet GetListByPage(string connectionString, string strTab, string strFields, string strWhere, string orderby, int startIndex, int endIndex)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("SELECT " + strFields + " FROM ( ");
                    strSql.Append(" SELECT ROW_NUMBER() OVER (");
                    if (!string.IsNullOrEmpty(orderby.Trim()))
                    {
                        strSql.Append("order by " + orderby);
                    }
                    strSql.Append(")AS Row, T.*  from " + strTab + " T ");
                    if (!string.IsNullOrEmpty(strWhere.Trim()))
                    {
                        strSql.Append(" WHERE " + strWhere);
                    }
                    strSql.Append(" ) TT");
                    strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
                    string cmdText = strSql.ToString();
                    PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, null);
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    cmd.Parameters.Clear();
                    return ds;
                }
            }
        }

        /// <summary>
        /// 生成要执行的命令
        /// </summary>
        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {

            try
            {
                // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
                if (cmdParms != null && cmdParms.Length > 0)
                    cmdText = cmdText.Replace("?", "@").Replace(":", "@");

                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                if (trans != null)
                    cmd.Transaction = trans;
                cmd.CommandType = cmdType;

                if (cmdParms != null)
                {
                    foreach (DbParameter parm in cmdParms)
                    {
                        // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
                        //parm.ParameterName = parm.ParameterName.Replace("?", "@").Replace(":", "@");
                        //if (parm.Value == null)
                        //    parm.Value = DBNull.Value;
                        //cmd.Parameters.Add(parm);

                        // 如果存在参数，则表示用户是用参数形式的SQL语句，可以替换
                        string name = parm.ParameterName.Replace("?", "@").Replace(":", "@");
                        object value = parm.Value == null ? DBNull.Value : parm.Value;
                        DbParameter p = new SqlParameter(name, value);
                        cmd.Parameters.Add(p);

                    }
                }
            }
            catch (Exception ex)
            {
                throw new NORMException(ExceptionType.DataBaseExceptoin, ex.Message);
            }
        }

    }

    #endregion
}
